﻿/*
#------------------------------------------------------------------------------
#-- Program Name:	[dbo].[spRestoreDatabase_Snapshot]
#-- Purpose:		Restores a database from a snapshot.  Due to the rule that
#--					only the one snapshot exists, all other snapshots will be
#--					deleted.
#--	Last Update:	05/31/2012
#--					For a complete history - please review comments in Version
#--					Control.
#-- Required XPs:	None
#------------------------------------------------------------------------------
*/
CREATE PROCEDURE [dbo].[spRestoreDatabase_Snapshot]
(
	@database_name			sysname,
	@label					sysname
)
WITH EXECUTE AS OWNER
AS

SET NOCOUNT ON 

--- Validate Parameters
IF ISNULL(@database_name, '') = '' OR ISNULL(@label, '') = ''
  BEGIN
	RAISERROR ('All parameters are required to restore from a snapshot.', 16, 1)
	RETURN
  END
IF NOT EXISTS (SELECT TOP 1 * FROM master.sys.databases WHERE name = @database_name)
  BEGIN
	RAISERROR ('The database does not exist.', 16, 1)
	RETURN
  END

--- Declare Local Variables
DECLARE		@snap_db_name sysname, @sSQL nvarchar(MAX), @file_name sysname, @directory_name nvarchar(MAX),
			@i int, @is_enterprise bit, @snap_exists bit, @sCommand nvarchar(4000),
			@sError varchar(1000), @sResults varchar(8000), @noarch bit, @has_rights bit
DECLARE		@progname sysname

--- Define the name of this program (for logs)
SELECT		@progname = 'spRestoreDatabase_Snapshot'

--- Determine if the user is authorized to do restores
EXEC dbo.spRestoreIsAllowed @database_name = @database_name, @is_allowed = @has_rights OUTPUT
IF @has_rights = 0
  BEGIN
	SELECT	@sError = 'You are not authorized to restore the database ' + @database_name
	RAISERROR (@sError, 16, 1)
  END

--- Initialize Local Variables
EXEC		@is_enterprise = dbo.spIsEnterpriseSQL
EXEC		@noarch = dbo.spIsDatabaseNoArchive @database_name
EXEC		@snap_exists = dbo.spBackupSnapshotExists @database_name = @database_name, @label = @label
SELECT		@database_name = LTRIM(RTRIM(@database_name)),
			@label = LTRIM(RTRIM(@label)),
			@snap_db_name = 'SNAP_' + @database_name + '_' + @label,
			@noarch = ISNULL(@noarch, 0),
			@is_enterprise = ISNULL(@is_enterprise, 0),
			@snap_exists = ISNULL(@snap_exists, 0)

--- Snapshots are only supported on the Developer/Enterprise Edition
IF @is_enterprise <> 1
  BEGIN
	RAISERROR ('Snapshots can only be used in Developer or Enterprise Edition', 16, 1)
	RETURN
  END

--- Validate that the snapshot exists
IF @snap_exists <> 1
  BEGIN
	RAISERROR ('The snapshot does not exist.', 16, 1)
	RETURN
  END

--- Remove all snapshots except the one to restore
SELECT	@sCommand = 'EXEC	dbo.spBackupSnapshotDrop @database_name = ''' + @database_name + ''', @keep_snap_db_name = ''' + @snap_db_name + ''''
EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
EXEC	(@sCommand)

--- Backup before restoring the database
IF @noarch = 1
	SELECT	@sCommand = 'EXEC	spBackupDifferential @database_name = ''' + @database_name + ''', @label = ''PRE_Restore'', @verify = 0'
ELSE
	SELECT	@sCommand = 'EXEC	spBackupLog @database_name = ''' + @database_name + ''', @label = ''PRE_Restore'', @verify = 0'

EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
EXEC	(@sCommand)

--- Drop existing connections
SELECT	@sCommand = 'EXEC	dbo.spRestoreKillProcesses @database_name = ''' + @database_name + ''''
EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
EXEC	(@sCommand)

--- Restore from the snapshot
BEGIN TRY
	SELECT		@sCommand = 'RESTORE DATABASE ' + QUOTENAME(@database_name) + ' FROM DATABASE_SNAPSHOT = ''' + @snap_db_name + ''';'
	EXEC		dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
	EXEC		(@sCommand)
END TRY
	--- If a command errors, exit the restore loop
	BEGIN CATCH
		--- Log the error - if possible
		SELECT @sResults = 'The database [' + @database_name + '] failed to restore the snapshot (' + @snap_db_name + ').' 
		IF ERROR_NUMBER() IS NOT NULL
			SELECT @sResults = @sResults + CHAR(13)
							+ 'ErrNo: ' + ISNULL(CAST(ERROR_NUMBER() as varchar(50)), '') + CHAR(13)
							+ 'ErrMsg: ' + ISNULL(ERROR_MESSAGE(), '') + CHAR(13)
							+ 'Proc: ' + ISNULL(ERROR_PROCEDURE(), '') + CHAR(13)
							+ 'LineNo: ' + ISNULL(CAST(ERROR_LINE() as varchar(10)), '')

		--- Print the error message to sysout
		PRINT @sResults

		--- Insert the error in the database
		INSERT INTO dbo.tblMaintenance (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
			VALUES (@database_name, GETDATE(), 'restore', 0, @sResults)

		--- Reraise the error to the caller
		RAISERROR (@sResults, 16, 1)
		RETURN
	END CATCH


--- Backup after a restore
SELECT	@sCommand = 'EXEC	spBackupDifferential @database_name = ''' + @database_name + ''', @label = ''POST_Restore'', @verify = 0'
EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
EXEC	(@sCommand)

SELECT	@sCommand = 'EXEC	spBackupLog @database_name = ''' + @database_name + ''', @verify = 0'
EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sCommand
EXEC	(@sCommand)

--- Log the successful restore
SELECT @sResults = 'The database [' + @database_name + '] was restored to the snapshot (' + @snap_db_name + ').' 
EXEC	dbo.spInsertLogEntry @program_name = @progname, @message = @sResults
INSERT INTO dbo.tblMaintenance (mnt_table_catalog, mnt_action_date, mnt_action_type, mnt_success, mnt_results)
	VALUES (@database_name, GETDATE(), 'restore', 1, @sResults)
